package com.qf.dao;

import com.qf.entity.Classes;
import com.qf.entity.Page;
import com.qf.entity.Student;
import com.qf.util.ConnectionUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 学生的dao层
 * description:
 * author: Ken
 * 公众号：Java架构栈
 */
public class StuDao {

    /**
     * 查询所有学生
     * @return
     */
    public List<Student> queryAll() throws SQLException {
        Connection connection = ConnectionUtils.getConnection();
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        //SQL - 进行学生和班级的连表查询
        String sql = "select " +
                " s.id, s.name, s.sex, s.cid, c.class_name, c.class_count" +
                " from student s join classes c on s.cid = c.id";

        List<Student> stus = new ArrayList<>();

        try {
            ps = connection.prepareStatement(sql);
            //直接查询
            resultSet = ps.executeQuery();
            //循环处理结果集
            while(resultSet.next()){
                //每个结果集对应一个学生 以及 一个班级
                Student student = new Student();
                Classes classes = new Classes();

                //学生对象放入结果集合 返回
                stus.add(student);

                //将结果集封装成学生对象
                student.setId(resultSet.getInt("id"))
                        .setName(resultSet.getString("name"))
                        .setSex(resultSet.getInt("sex"))
                        //重要 - 将班级对象赋值给学生对象 表示两者间的关联关系
                        .setClasses(classes);

//                //将结果集封装成班级
                classes.setId(resultSet.getInt("cid"))
                    .setClassName(resultSet.getString("class_name"))
                    .setClassCount(resultSet.getInt("class_count"));
            }
        } finally {
            ConnectionUtils.close(connection, ps, resultSet);
        }

        return stus;
    }

    /**
     * 新增学生信息
     * @return
     */
    public int insert(Student student) throws SQLException {
        Connection connection = ConnectionUtils.getConnection();
        System.out.println("新增学生获得的连接：" + connection);
        PreparedStatement ps = null;
        String sql = "insert into student value(?,?,?,?)";

        try {
            ps = connection.prepareStatement(sql);
            ps.setObject(1, student.getId());
            ps.setObject(2, student.getName());
            ps.setObject(3, student.getSex());
            ps.setObject(4, student.getCid());
            return ps.executeUpdate();
        } finally {
            ConnectionUtils.close(connection, ps, null);
        }
    }

    /**
     * 返回学生的总数
     * @return
     */
    public int queryAllStusSum(){
        String sql = "select count(*) from student";
        Connection conn = ConnectionUtils.getConnection();
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            ps = conn.prepareStatement(sql);
            //执行查询
            resultSet = ps.executeQuery();

            //获得总条数
            if (resultSet.next()) {
               return resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectionUtils.close(conn, ps, resultSet);
        }
        return 0;
    }

    /**
     * 根据分页查询指定页的学生数据
     * @param page
     * @return
     */
    public List<Student> queryStusPage(Page page){
        String sql = "select" +
                " s.id, s.name, s.sex, s.cid, c.class_name, c.class_count" +
                " from student s join classes c on s.cid = c.id limit ?,?";
        Connection connection = ConnectionUtils.getConnection();
        System.out.println("当前获得的连接对象：" + connection);
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        List<Student> stus = new ArrayList<>();
        try {
            ps = connection.prepareStatement(sql);
            //设置参数
            ps.setObject(1, (page.getPage() - 1) * page.getPageSize());
            ps.setObject(2, page.getPageSize());
            //执行查询
            resultSet = ps.executeQuery();
            //循环处理结果集
            while(resultSet.next()){
                //每个结果集对应一个学生 以及 一个班级
                Student student = new Student();
                Classes classes = new Classes();

                //学生对象放入结果集合 返回
                stus.add(student);

                //将结果集封装成学生对象
                student.setId(resultSet.getInt("id"))
                        .setName(resultSet.getString("name"))
                        .setSex(resultSet.getInt("sex"))
                        //重要 - 将班级对象赋值给学生对象 表示两者间的关联关系
                        .setClasses(classes);

//                //将结果集封装成班级
                classes.setId(resultSet.getInt("cid"))
                        .setClassName(resultSet.getString("class_name"))
                        .setClassCount(resultSet.getInt("class_count"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectionUtils.close(connection, ps, resultSet);
        }
        return stus;
    }
}
